package com.atguigu.Flink.sql.query;

import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.TableEnvironment;

public class Flink02_GroupingSets {
    public static void main(String[] args) {
        TableEnvironment tableEnv = TableEnvironment.create(EnvironmentSettings.newInstance().build());

        // grouping sets
        tableEnv.sqlQuery(
                "select id , ts  , count(*) cnt from \n" +
                        "                ( VALUES \n" +
                        "                        ('ws1' , 1000 , 1 ) ,\n" +
                        "                        ('ws2' , 1000 , 2 ) ,\n" +
                        "                        ('ws1' , 3000 , 3 ) ,\n" +
                        "                        ('ws2' , 4000 , 4 ) ,\n" +
                        "                        ('ws3' , 5000 , 2 ) ,\n" +
                        "                        ('ws4' , 6000 , 5 )) \n" +
                        "                AS ws (id , ts , vc )\n" +
                        "                group by GROUPING SETS (\n" +
                        "                        (id,ts) ,\n" +
                        "                        (id) ,\n" +
                        "                        (ts)\n" +
                        "                ) \n"
        );//.execute().print();

        // rollup
        tableEnv.sqlQuery(
                "select id , ts  , count(*) cnt from \n" +
                        "                ( VALUES \n" +
                        "                        ('ws1' , 1000 , 1 ) ,\n" +
                        "                        ('ws2' , 1000 , 2 ) ,\n" +
                        "                        ('ws1' , 3000 , 3 ) ,\n" +
                        "                        ('ws2' , 4000 , 4 ) ,\n" +
                        "                        ('ws3' , 5000 , 2 ) ,\n" +
                        "                        ('ws4' , 6000 , 5 )) \n" +
                        "                AS ws (id , ts , vc )\n" +
                        "                group by ROLLUP(\n" +
                        "                   id ,ts\n" +
                        "                )\n"
        ) ; //.execute().print();


        // cube
        tableEnv.sqlQuery(
                "select id , ts  , count(*) cnt from \n" +
                        "                ( VALUES \n" +
                        "                        ('ws1' , 1000 , 1 ) ,\n" +
                        "                        ('ws2' , 1000 , 2 ) ,\n" +
                        "                        ('ws1' , 3000 , 3 ) ,\n" +
                        "                        ('ws2' , 4000 , 4 ) ,\n" +
                        "                        ('ws3' , 5000 , 2 ) ,\n" +
                        "                        ('ws4' , 6000 , 5 )) \n" +
                        "                AS ws (id , ts , vc )\n" +
                        "                group by CUBE(\n" +
                        "                  id ,ts\n" +
                        "                )\n"
        ).execute().print();
    }
}
